using System;
using System.Collections;
using System.Collections.Generic;

namespace FreeFlow.Administration
{
	/// <summary>
	/// A collection of reports.
	/// </summary>
	public sealed class ReportCollection : DatabaseAware, IList, IEnumerable<Report>
	{
    private List<Report> reports;
    /// <summary>
    /// Initializes a new instance of the <see cref="ReportCollection"/> class.
    /// </summary>
    /// <param name="server">The server.</param>
		public ReportCollection(Server server) : base(server)
		{
      reports = new List<Report>();

      if (Server.DatabaseType == DatabaseType.SqlServer)
        AddReport("100 folders with most activity",
          "SELECT TOP 100 eFolderID AS \"Folder ID\", eMapName AS \"Map name\", COUNT(*) AS Activity, MAX(eEventTime) AS \"Last activity\" FROM eEvent " +
          server.WithNoLock + " GROUP BY eFolderID, eMapName ORDER BY Activity DESC",
          "Shows the folders that have been moved through the most actions. This may help to identify folders that could be deleted to save storage space and improve performance. " +
          "It may also help to identify folders that are stuck in an infinite loop.");

      AddReport("Folders updated in the last 24 hours",
        "SELECT eFolderID AS \"Folder ID\", eFolderName AS \"Folder name\", eSubject AS \"Subject\", eMapName AS \"Map name\", eUpdated AS Updated FROM eFolder WHERE eUpdated > " + Server.GetDate + "-1 ORDER BY eUpdated",
        "Shows all folders updated in the last 24 hours.");
		  AddReport("Locked folders", 
        "SELECT eFolderID AS \"Folder ID\", eFolderName AS \"Folder name\", eMapName AS \"Map name\", " +
        "eActionUser AS \"User\", eActionTime AS \"Locked since\", eActionName AS \"Action\" FROM eFolder WHERE eActionTime IS NOT NULL ORDER BY eActionTime",
        "Shows all folders currently locked by users.");
      AddReport("Child folders without parents",
        "SELECT eFolderID AS \"Folder ID\", eFolderName AS \"Folder name\", eParent AS \"Parent\", " +
        "eMapName AS \"Map name\" FROM eFolder f WHERE f.eParent IS NOT NULL AND NOT EXISTS (SELECT * FROM eFolder g WHERE g.eFolderID = f.eParent)",
        "Shows folders that have a parent folder ID specified but the parent folder does not exist.");
      AddReport("User/group stage folders not on a To Do list", 
        "select eFolderID AS \"Folder ID\", eFolderName AS \"Folder name\", eFolder.eMapName AS \"Map name\", eFolder.eStageName AS \"Stage name\", eFolder.eSubject AS \"Subject\" from eFolder, eStage where eFolder.eMapName = eStage.eMapName AND eFolder.eStageName = eStage.eStageName AND ((eStage.eStageType = 1) or (eStage.eStageType = 2)) AND NOT eFolderID IN (SELECT eFolderID FROM eAlert WHERE eAlertType = ' ')",
        "Shows all folders that are currently at a user or group stage but are not on anybody's To Do list. This may be intentional, but it could be due to a procedural error or incorrect assignment of roles.");
      AddReport("Folders by map", "select eMap.eMapName AS Map, COUNT(eFolder.eFolderID) As \"Number of folders\" from eMap LEFT JOIN eFolder ON eFolder.eMapName = eMap.eMapName WHERE eMap.eAdmin=0 group by eMap.eMapName order by eMap.eMapName",
        "Shows the number of folders created for each map. This will give a useful indication of which are your most active maps and where bottlenecks are occurring.");

      if (Server.DatabaseType == DatabaseType.SqlServer)
        AddReport("Folders by map and stage",
          "select eMap.eMapName AS Map, eStage.eStageName AS Stage, COUNT(eFolder.eFolderID) As \"Number of folders\" from eMap LEFT JOIN eStage ON eStage.eMapName=eMap.eMapName LEFT JOIN eFolder ON eFolder.eStageName = eStage.eStageName AND eFolder.eMapName=eStage.eMapName group by eMap.eMapName, eStage.eStageName union " +
          "select eMap.eMapName, '[All]' As Stage, COUNT(eFolder.eFolderID) As \"Number of folders\" from eMap LEFT JOIN eFolder ON eFolder.eMapName = eMap.eMapName WHERE eMap.eAdmin=0 group by eMap.eMapName order by eMap.eMapName, Stage",
          "Shows the number of folders created for each map and the number of folders at each stage. This will give a useful indication of which are your most active maps and where bottlenecks are occurring. Note the totals may be greater than the sum of folders at each stage because the total will also include folders not yet at a stage.");
      
      AddReport("Number of flags", "SELECT eFlagName AS Flag, COUNT(eFolderID) AS \"Folder Count\" FROM eWait WHERE eFlagName <> ' ' GROUP BY eFlagName ORDER BY eFlagName",
        "Shows the number of folders waiting on each flag");
      AddReport("Activity by action",
        "select eAction.eMapName AS Map, eAction.eStageName AS Stage, eAction.eActionName AS Action, COUNT(eEvent.eActionName) AS Activity from eAction LEFT JOIN eEvent ON eAction.eMapName = eEvent.eMapName AND (eAction.eStageName = eEvent.eFromStage OR (eAction.eStageName = ' ' AND eEvent.eFromStage IS NULL)) AND eAction.eActionName = eEvent.eActionName LEFT JOIN eStart ON eAction.eMapName = eStart.eMapName WHERE eStart.eFormType = 'B' OR eStart.eFormType IS NULL GROUP BY eAction.eMapName, eAction.eStageName, eAction.eActionName ORDER BY Activity DESC",
        "Shows which actions have been invoked most often. Actions that are infrequently used may be candidates for removal from the process.");
      AddReport("Old stages",
        "select eMap.eProcedureName AS \"Procedure\", eMap.eMapName AS Map, estage.eStageName AS Stage, COUNT(eFolderID) As \"Number of folders\" FROM eMap INNER JOIN eStage ON eStage.eMapName = eMap.eMapName AND eStage.eLoadedTime <> eMap.eLoadedTime LEFT JOIN eFolder ON eFolder.eMapName = eMap.eMapName AND eFolder.eStageName = eStage.eStageName group by eMap.eProcedureName, eMap.eMapName, eStage.eStageName ORDER BY eMap.eMapName, eStage.eStageName",
        "Shows stages that are no longer part of your published procedure but still exist in the database. If folders still exist at the stage, you may want to move them onto a stage that is still available in the procedure. If no folders are at the stage you can purge the stage.");

      if (Server.DatabaseType == DatabaseType.SqlServer)
        AddReport("Orphaned attachments",
          "SELECT SUBSTRING(eKey, 3, 31) AS \"Original Folder ID\", SUBSTRING(eKey, 35, 1000) AS \"File name\", eSize AS \"Size\"  FROM eAttachment WHERE eKey LIKE '0%' AND SUBSTRING(eKey, 3, 31) NOT IN (SELECT eFolderID FROM eFolder)",
          "Shows folder attachments that are not associated with a folder that exists. Unless these attachments contain important information, thay can be deleted.");

      // user reports
      string sql;
      if (Server.SchemaVersion >= SchemaVersion.Version70)
        sql = "select DISTINCT eRoleName As Role from eRole WHERE eRoleName NOT IN (SELECT eRoleName FROM eAssignment) AND eDynamic=0";
      else
        sql = "select DISTINCT eRoleName As Role from eRole WHERE eRoleName NOT IN (SELECT eRoleName FROM eAssignment)";
      AddReport("Roles not assigned to a user", sql,
        "Shows roles that are not assigned to a user. For certain roles this may be design, but generally roles should be assigned to a user");
      AddReport("Users with blank passwords",
        string.Format("SELECT eUserName AS \"User\" FROM eUser WHERE ePassword = '{0}'", User.EncodedBlankPassword),
        "Shows users who have a blank password, which is a potential security problem");
      AddReport("Users with the same password", "SELECT ePassword As Password, eUserName AS \"User\" FROM eUser WHERE ePassword IN (select ePassword from eUser GROUP BY ePassword HAVING COUNT(*) > 1) ORDER BY ePassword",
        "Shows users that share the same password. This may mean the users have insecure passwords or are using the default password they were originally given");
    }

    private void AddReport(string name, string sql, string description)
    {
      reports.Add(new Report(Server, name, sql, description));
    }

    /// <summary>
    /// Returns an enumerator that can iterate through a collection.
    /// </summary>
    /// <returns>
    /// An <see cref="T:System.Collections.IEnumerator"/>
    /// that can be used to iterate through the collection.
    /// </returns>
    IEnumerator IEnumerable.GetEnumerator()
    {
      return reports.GetEnumerator();
    }

    #region IEnumerable<Report> Members

    /// <summary>
    /// Returns an enumerator that iterates through the collection.
    /// </summary>
    /// <returns>
    /// A <see cref="T:System.Collections.Generic.IEnumerator`1"/> that can be used to iterate through the collection.
    /// </returns>
    public IEnumerator<Report> GetEnumerator()
    {
      return reports.GetEnumerator();
    }

    #endregion

    /// <summary>
    /// Gets the <see cref="Report"/> at the specified index.
    /// </summary>
    public Report this[int index]
    {
      get
      {
        return (Report)reports[index];
      }
    }

    #region IList Members

    /// <summary>
    /// Gets a value indicating whether the <see cref="T:System.Collections.IList"/> is read-only.
    /// </summary>
    public bool IsReadOnly
    {
      get
      {
        return true;
      }
    }

    object IList.this[int index]
    {
      get
      {
        return reports[index];
      }
      set
      {
        throw new NotSupportedException(readOnlyListMessage);
      }
    }

    void IList.RemoveAt(int index)
    {
      throw new NotSupportedException(readOnlyListMessage);
    }

    void IList.Insert(int index, object value)
    {
      throw new NotSupportedException(readOnlyListMessage);
    }

    void IList.Remove(object value)
    {
      throw new NotSupportedException(readOnlyListMessage);
    }

    /// <summary>
    /// Determines whether the <see cref="T:System.Collections.IList"/> contains a specific value.
    /// </summary>
    /// <param name="value">The <see cref="T:System.Object"/> to locate in the <see cref="T:System.Collections.IList"/>.</param>
    /// <returns>
    /// 	<see langword="true"/> if the <see cref="T:System.Object"/>
    /// is found in the <see cref="T:System.Collections.IList"/>; otherwise, <see langword="false"/>.
    /// </returns>
    public bool Contains(object value)
    {
      return reports.Contains((Report)value);
    }

    void IList.Clear()
    {
      throw new NotSupportedException(readOnlyListMessage);
    }

    /// <summary>
    /// Determines the index of a
    /// specific item in the <see cref="T:System.Collections.IList"/>.
    /// </summary>
    /// <param name="value">The <see cref="T:System.Object"/> to locate in the <see cref="T:System.Collections.IList"/>.</param>
    /// <returns>
    /// The index of <paramref name="value"/> if found in the list; otherwise, -1.
    /// </returns>
    public int IndexOf(object value)
    {
      return reports.IndexOf((Report)value);
    }

    int IList.Add(object value)
    {
      throw new NotSupportedException(readOnlyListMessage);
    }

    /// <summary>
    /// Gets a value indicating whether the <see cref="T:System.Collections.IList"/> has a fixed size.
    /// </summary>
    public bool IsFixedSize
    {
      get
      {
        return true;
      }
    }

    #endregion
    #region ICollection Members

    /// <summary>
    /// Gets a value
    /// indicating whether access to the <see cref="T:System.Collections.ICollection"/> is synchronized
    /// (thread-safe).
    /// </summary>
    public bool IsSynchronized
    {
      get
      {
        return false;
      }
    }

    /// <summary>
    /// Gets the number of reports in the collection.
    /// </summary>
    public int Count
    {
      get
      {
        return reports.Count;
      }
    }

    /// <summary>
    /// Copies the elements of
    /// the <see cref="T:System.Collections.ICollection"/> to an <see cref="T:System.Array"/>, starting at a particular <see cref="T:System.Array"/> index.
    /// </summary>
    /// <param name="array">The one-dimensional <see cref="T:System.Array"/> that is the destination of the elements copied from <see cref="T:System.Collections.ICollection"/>. The <see cref="T:System.Array"/> must have zero-based indexing.</param>
    /// <param name="index">The zero-based index in <paramref name="array"/> at which copying begins.</param>
    /// <exception cref="T:System.ArgumentNullException">
    /// 	<paramref name="array"/> is <see langword="null"/>.</exception>
    /// <exception cref="T:System.ArgumentOutOfRangeException">
    /// 	<paramref name="index"/> is less than zero.</exception>
    /// <exception cref="T:System.ArgumentException">
    /// 	<para>
    /// 		<paramref name="array"/> is multidimensional.</para>
    /// 	<para>-or-</para>
    /// 	<para>
    /// 		<paramref name="index"/> is equal to or greater than the length of <paramref name="array"/>.</para>
    /// 	<para>-or-</para>
    /// 	<para>The number of elements in the source <see cref="T:System.Collections.ICollection"/> is greater than the available space from <paramref name="index"/> to the end of the destination <paramref name="array"/>.</para>
    /// </exception>
    /// <exception cref="T:System.InvalidCastException">The type of the source <see cref="T:System.Collections.ICollection"/> cannot be cast automatically to the type of the destination <paramref name="array"/>.</exception>
    public void CopyTo(Report[] array, int index)
    {
      reports.CopyTo(array, index);
    }

    void ICollection.CopyTo(Array array, int index)
    {
      CopyTo((Report[])array, index);
    }

    /// <summary>
    /// Gets an object that
    /// can be used to synchronize access to the <see cref="T:System.Collections.ICollection"/>.
    /// </summary>
    public object SyncRoot
    {
      get
      {
        return null;
      }
    }

    #endregion
  }
}
